﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using Light.Utility;

namespace Light.Framework
{
    /// <summary>
    /// 数据表查询器
    /// </summary>
    public class DataTableMediator
    {
        /// <summary>
        ///  获取符合条件的所有记录
        /// </summary>
        /// <param name="tableOrViewName">表名或视图名</param>
        /// <param name="searchArgs">查询参数，可为null</param>
        /// <param name="resultOrder">排序参数，可为null</param>
        /// <param name="resultColumn">需要查询的列</param>
        /// <returns></returns>
        public static DataTable GetAll(string tableOrViewName, SearchArgs searchArgs, ResultOrder resultOrder, ResultColumn resultColumn)
        {
            return GetAll(tableOrViewName, searchArgs, resultOrder, resultColumn, 0);
        }


        /// <summary>
        ///  获取符合条件的所有记录
        /// </summary>
        /// <param name="tableOrViewName">表名或视图名</param>
        /// <param name="searchArgs">查询参数，可为null</param>
        /// <param name="resultOrder">排序参数，可为null</param>
        /// <param name="resultColumn">需要查询的列</param>
        /// <param name="resultCount">需要返回的结果数量</param>
        /// <returns></returns>
        public static DataTable GetAll(string tableOrViewName, SearchArgs searchArgs, ResultOrder resultOrder, ResultColumn resultColumn, int resultCount)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();
            if (resultOrder == null)
                resultOrder = new ResultOrder();
            if (resultColumn == null)
                resultColumn = new ResultColumn();

            string top = "";
            if (resultCount > 0)
                top = " TOP " + resultCount;

            string columns = resultColumn.ToString();
            string where = searchArgs.ToString();
            string order = resultOrder.ToString();


            List<IDataParameter> listParams = searchArgs.GetDataParameters();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                if (!String.IsNullOrEmpty(order))
                    order = " ORDER BY " + order;

                string sql = string.Format(@"SELECT {0} {1} FROM  {2} {3} {4}", top, columns, tableOrViewName, where, order);

                DataSet ds = executer.ExecuteDataset(sql, listParams.ToArray());

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
        }


        /// <summary>
        /// 单 表或视图 分页查询，返回DataTable
        /// </summary>
        /// <param name="tableOrViewName">表名或视图名</param>
        /// <param name="searchArgs">查询参数，可为null</param>
        /// <param name="pageArgs">分页参数，不能为null</param>
        /// <param name="resultOrder">排序参数，不能为null</param>
        /// <param name="resultColumn">需要查询的列，可为null</param>
        /// <returns></returns>
        public static DataTable Search(string tableOrViewName, SearchArgs searchArgs, PageArgs pageArgs, ResultOrder resultOrder, ResultColumn resultColumn)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();
            if (resultOrder == null && resultOrder.IsEmpty)
                throw new Exception("排序参数 ResultOrder 不能为空");
            if (pageArgs == null)
                throw new Exception("分页参数 PageArgs 不能为空");
            if (resultColumn == null)
                resultColumn = new ResultColumn();

            string columns = resultColumn.ToString();
            string whereSql = searchArgs.ToString();
            string orderSql = resultOrder.ToString();
            List<IDataParameter> listParams = searchArgs.GetDataParameters();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {

                //获取总记录条数
                string countSql = string.Format("SELECT COUNT(*) FROM {0} {1}", tableOrViewName, whereSql);
                pageArgs.TotalResults = StringHelper.ToInt(executer.ExecuteScalar(countSql, listParams.ToArray()));

                //查询结果集
                string sql = string.Format(@"
                                SELECT {0}
                                FROM {1}
                                 {2}
                                ORDER BY {3}
                                OFFSET {4} ROWS
                                FETCH NEXT {5} ROWS ONLY", columns, tableOrViewName, whereSql, orderSql, (pageArgs.PageIndex - 1) * pageArgs.PageSize, pageArgs.PageSize);

                DataSet ds = executer.ExecuteDataset(sql, listParams.ToArray());

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
        }

        /// <summary>
        /// 金额求和
        /// </summary>
        /// <param name="tableOrViewName">表或视图名称</param>
        /// <param name="columnName">数据库列名</param>
        /// <param name="searchArgs">查询参数</param>
        /// <returns></returns>
        public static decimal Sum(string tableOrViewName, string columnName, SearchArgs searchArgs)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();

            string where = searchArgs.ToString();
            List<IDataParameter> listParams = searchArgs.GetDataParameters();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                string sql = string.Format(@"SELECT SUM({0}) FROM {1} {2}", columnName, tableOrViewName, where);

                object result = executer.ExecuteScalar(sql, listParams.ToArray());
                return StringHelper.ToDecimal(result);
            }
        }


        /// <summary>
        /// 求总记录数
        /// </summary>
        /// <param name="tableOrViewName">表或视图名称</param>
        /// <param name="searchArgs">查询参数</param>
        /// <returns></returns>
        public static int Count(string tableOrViewName, SearchArgs searchArgs)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();

            string whereSql = searchArgs.ToString();
            List<IDataParameter> listParams = searchArgs.GetDataParameters();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                string sql = string.Format("SELECT COUNT(*) FROM {0} {1}", tableOrViewName, whereSql);

                object result = executer.ExecuteScalar(sql, listParams.ToArray());
                return StringHelper.ToInt(result);
            }
        }

    }
}
